<?php

class DBManager extends PDO {

    // =========================================================================
    // 分类相关
    // =========================================================================

    // -------------------------------------------------------------------------
    // 增
    // -------------------------------------------------------------------------

    public function insertIntoCategory($arr) {

        extract($arr);

        if (!empty(trim($targetSortNumber))) {
            $sql = <<<BBB
                update category 
                set sortNumber = sortNumber + 1
                where sortNumber >= :targetSortNumber
            BBB;
            $stmt = $this->prepare($sql);
            $stmt->bindParam(':targetSortNumber', $targetSortNumber);
            $stmt->execute();

            // 由于指定了 $targetSortNumber，所以这里要将它的值改成 $targetSortNumber 的值
            $sortNumber = $targetSortNumber;
        }

        $sql = <<<BBB
            insert into category (title, description, parentId, sortNumber, icon, timeCreate, timeLastMove)
                        values (:title, :description, :parentId, :sortNumber, :icon, :timeCreate, :timeLastMove)
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':title', $title);
        $stmt->bindParam(':description', $description);
        $stmt->bindParam(':parentId', $parentId);
        $stmt->bindParam(':sortNumber', $sortNumber);
        $stmt->bindParam(':icon', $icon);
        $stmt->bindParam(':timeCreate', $timeCreate);
        $stmt->bindParam(':timeLastMove', $timeLastMove);
        $stmt->execute();

    }

    // -------------------------------------------------------------------------
    // 删
    // -------------------------------------------------------------------------

    /**
     * 删除分类
     * @param $id
     * @return void
     */
    public function deleteCategory($id) {

        $sql = <<<BBB
            delete from category
            where id = :id
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':id', $id);
        $stmt->execute();

    }

    /**
     * 删除分类下的所有链接
     * @param $id
     * @return void
     */
    public function deleteLinksUnderCategory($id) {

        $sql = <<<BBB
            delete from links
            where categoryId = :id
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':id', $id);
        $stmt->execute();

    }

    // -------------------------------------------------------------------------
    // 改
    // -------------------------------------------------------------------------

    public function updateCategory($id, $arr) {

        extract($arr);

        $sql = <<<BBB
            update category 
            set title = :title,
                description = :description,
                timeLastEdit = :timeLastEdit
            where id = :id
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':title', $title);
        $stmt->bindParam(':description', $description);
        $stmt->bindParam(':timeLastEdit', $timeLastEdit);
        $stmt->bindParam(':id', $id);
        $stmt->execute();

    }

    public function changeCategoryAttrSortNumber($id, $sortNumber) {

        $sql = <<<BBB
            update category
            set sortNumber = :sortNumber
            where id = :id
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':sortNumber', $sortNumber);
        $stmt->bindParam(':id', $id);

        return $stmt->execute();

    }

    public function changeSortNumberOfCategoryFromAToB($source, $target) {

        // 第一步
        // 把要移动的分类的 sortnumber 修改为0，避免在第二步的操作中被修改，导致在第三步中无法定位
        $sql = <<<BBB
            update category
            set sortNumber = 0
            where sortNumber = :source
        BBB;
        $stmt = $this->prepare($sql);
        $stmt->bindParam(':source', $source);
        $stmt->execute();

        // 第二步
        $sql = <<<BBB
            update category
            set sortNumber = sortNumber + 1
            where sortNumber >= :targetSortNumber
        BBB;
        $stmt = $this->prepare($sql);
        $stmt->bindParam(':targetSortNumber', $target);
        $stmt->execute();

        // 第三步
        $sql = <<<BBB
            update category
            set sortNumber = :targetSortNumber
            where sortNumber = 0
        BBB;
        $stmt = $this->prepare($sql);
        $stmt->bindParam(':targetSortNumber', $target);
        $stmt->execute();

    }

    // -------------------------------------------------------------------------
    // 查
    // -------------------------------------------------------------------------

    /**
     * 获取某个分类下的子级分类的总数
     * @return int|mixed
     */
    public function getNumberOfSubCategories() {
        $sql = <<<BBB
            select count(*) from category
            where trim(parentId) != ""
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->execute();

        return $stmt->fetchColumn() ?: 0;
    }

    /**
     * 获取父级分类的总数
     * @return int|mixed
     */
    public function getNumberOfCategories() {
        $sql = <<<BBB
            select count(*) from category
            where parentId is null or trim(parentId) = ""
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->execute();

        return $stmt->fetchColumn() ?: 0;
    }

    public function getListOfCategories() {
        $sql = <<<BBB
            select * from category
            where trim(parentId) = "" or parentId is null
            order by sortNumber asc
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->execute();

        $data = [];
        while ($row = $stmt->fetch()) {
            $arr = [];
            $arr["id"] = $row["id"];
            $arr["title"] = $row["title"];
            $arr["description"] = $row["description"];
            $arr["sortNumber"] = $row["sortNumber"];
            $data[] = $arr;
        }
        return $data;
    }

    /**
     * 判断指定的「分类名」是否已经存在
     * @param $title
     * @return bool
     */
    public function checkIfCategoryExistsByTitle($title) {

        $sql = <<<BBB
            SELECT * FROM category WHERE title = :title;
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':title', $title);
        $stmt->execute();

        $result = $stmt->fetch();
        if ($result) {
            return true;
        } else {
            return false;
        }

    }

    /**
     * 判断指定的分类id是否存在
     * @param $id
     * @return bool
     */
    public function checkIfCategoryExistsById($id) {

        $sql = <<<BBB
            SELECT * FROM category
            WHERE id = :id;
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':id', $id);
        $stmt->execute();

        $result = $stmt->fetch();
        if ($result) {
            return true;
        } else {
            return false;
        }

    }

    /**
     * 取得分类详细信息，根据id
     * @param $id
     * @return false|mixed
     */
    public function getCategoryDetailsById($id) {

        $sql = <<<BBB
            SELECT * FROM category
            WHERE id = :id;
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':id', $id);
        $stmt->execute();
        $result = $stmt->fetch();

        if ($result) {
            return $result;
        } else {
            return false;
        }

    }

    /**
     * 取得分类详细信息，根据分类标题
     * @param $title
     * @return false|mixed
     */
    public function getCategoryDetailsByTitle($title) {

        $sql = <<<BBB
            SELECT * FROM category
            WHERE title = :title;
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':title', $title);
        $stmt->execute();
        $result = $stmt->fetch();

        if ($result) {
            return $result;
        } else {
            return false;
        }

    }

    public function getNumberOfLinksInCategory($categoryId) {

        $sql = <<<BBB
            select count(*) from links
            where categoryId = :categoryId
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':categoryId', $categoryId);
        $stmt->execute();

        return $stmt->fetchColumn() ?: 0;

    }

    /**
     * 取得指定分类下的所有网址的总点击次数
     * @param $categoryId
     * @return int|mixed
     */
    public function getTotalTimesOfClickOfCategory($categoryId) {

        $sql = <<<BBB
            select sum(timesOfClick) from links
            where categoryId = :categoryId and timesOfClick > 0
        BBB;
        $stmt = $this->prepare($sql);
        $stmt->bindParam(':categoryId', $categoryId);
        $stmt->execute();

        return $stmt->fetchColumn() ?: 0;

    }

    /**
     * 取得所有网址的总点击次数
     * @return int
     */
    public function getTotalTimesOfClick() {

        $sql = <<<BBB
            select sum(timesOfClick) from links
            where timesOfClick > 0
        BBB;
        $stmt = $this->prepare($sql);
        $stmt->execute();

        return $stmt->fetchColumn() ?: 0;

    }

    // =========================================================================
    // 链接相关
    // =========================================================================

    // -------------------------------------------------------------------------
    // 增
    // -------------------------------------------------------------------------

    public function insertIntoLinks($arr) {

        extract($arr);

        if (!empty(trim($targetSortNumber))) {
            $sql = <<<BBB
                update links
                set sortNumber = sortNumber + 1
                where sortNumber >= :targetSortNumber
            BBB;
            $stmt = $this->prepare($sql);
            $stmt->bindParam(':targetSortNumber', $targetSortNumber);
            $stmt->execute();

            // 由于指定了 $targetSortNumber，所以这里要将它的值改成 $targetSortNumber 的值
            $sortNumber = $targetSortNumber;
        }

        $sql = <<<BBB
            insert into links (title, url, categoryId, description, sortNumber, timeCreate, timesOfClick)
                        values (:title, :url, :categoryId, :description, :sortNumber, :timeCreate, :timesOfClick)
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':title', $title);
        $stmt->bindParam(':url', $url);
        $stmt->bindParam(':categoryId', $categoryId);
        $stmt->bindParam(':description', $description);
        $stmt->bindParam(':sortNumber', $sortNumber);
        $stmt->bindParam(':timeCreate', $timeCreate);
        $stmt->bindParam(':timesOfClick', $timesOfClick);
        $stmt->execute();

    }

    // -------------------------------------------------------------------------
    // 删
    // -------------------------------------------------------------------------

    public function deleteLink($id) {

        $sql = <<<BBB
            delete from links
            where id = :id
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':id', $id);
        $stmt->execute();

    }

    // -------------------------------------------------------------------------
    // 改
    // -------------------------------------------------------------------------

    public function updateLink($id, $arr) {

        extract($arr);

        $sql = <<<BBB
            update links
            set title = :title,
                url = :url,
                categoryId = :categoryId,
                sortNumber = :sortNumber,
                description = :description,
                timeLastEdit = :timeLastEdit,
                timeLastMove = :timeLastMove
            where id = :id
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':title', $title);
        $stmt->bindParam(':url', $url);
        $stmt->bindParam(':categoryId', $categoryId);
        $stmt->bindParam(':sortNumber', $sortNumber);
        $stmt->bindParam(':description', $description);
        $stmt->bindParam(':timeLastEdit', $timeLastEdit);
        $stmt->bindParam(':timeLastMove', $timeLastMove);
        $stmt->bindParam(':id', $id);
        $stmt->execute();

    }

    public function changeLinkAttrSortNumber($id, $sortNumber) {

        $sql = <<<BBB
            update links
            set sortNumber = :sortNumber
            where id = :id
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':sortNumber', $sortNumber);
        $stmt->bindParam(':id', $id);

        return $stmt->execute();

    }

    public function changeSortNumberOfLinkFromAToB($source, $target) {

        // 第一步
        // 把要移动的链接的 sortnumber 修改为0，避免在第二步的操作中被修改，导致在第三步中无法定位
        $sql = <<<BBB
            update links
            set sortNumber = 0
            where sortNumber = :source
        BBB;
        $stmt = $this->prepare($sql);
        $stmt->bindParam(':source', $source);
        $stmt->execute();

        // 第二步
        $sql = <<<BBB
            update links
            set sortNumber = sortNumber + 1
            where sortNumber >= :targetSortNumber
        BBB;
        $stmt = $this->prepare($sql);
        $stmt->bindParam(':targetSortNumber', $target);
        $stmt->execute();

        // 第三步
        $sql = <<<BBB
            update links
            set sortNumber = :targetSortNumber
            where sortNumber = 0
        BBB;
        $stmt = $this->prepare($sql);
        $stmt->bindParam(':targetSortNumber', $target);
        $stmt->execute();
    }

    public function changeLinkAttrCategoryId($id, $categoryId, $timeLastMove) {

        $sql = <<<BBB
            update links
            set categoryId = :categoryId,
                timeLastMove = :timeLastMove
            where id = :id;
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':categoryId', $categoryId);
        $stmt->bindParam(':timeLastMove', $timeLastMove);
        $stmt->bindParam(':id', $id);
        return $stmt->execute();

    }

    public function changeLinkAttrTimeLastClick($id, $timeLastClick) {

        $sql = <<<BBB
            update links
            set timeLastClick = :timeLastClick
            where id = :id;
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':timeLastClick', $timeLastClick);
        $stmt->bindParam(':id', $id);
        return $stmt->execute();

    }

    /**
     * 增加链接点击数量的值
     * @param $linkId
     * @return void
     */
    public function increaseLinkTimesOfClick($linkId) {

        $sql = <<<BBB
            update links
            set timesOfClick = timesOfClick + 1
            where id = :id
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':id', $linkId);
        $stmt->execute();

    }

    // -------------------------------------------------------------------------
    // 查
    // -------------------------------------------------------------------------

    public function getListOfLinksInCategory($categoryId, $order = "sortNumber") {

        switch ($order) {
            case "sortNumber":
                $sqlOrder = "order by sortNumber asc";
                break;
            case "timesOfClick":
                $sqlOrder = "order by timesOfClick desc";
                break;
            case "timeCreate":
                $sqlOrder = "order by timeCreate desc";
                break;
            case "timeLastEdit":
                // 如果链接 timeLastEdit 字段的值为空，则使用 id 降序排列
                $sqlOrder = "order by timeLastEdit desc, id desc";
                break;
            case "timeLastMove":
                // 如果链接 timeLastMove 字段的值为空，则使用 id 降序排列
                $sqlOrder = "order by timeLastMove desc, id desc";
                break;
            case "timeLastClick":
                // 如果链接 timeLastClick 字段的值为空，则使用 id 降序排列
                $sqlOrder = "order by timeLastClick desc, id desc";
                break;
        }

        // 拼接 SQL 语句
        $sql = <<<BBB
            select * from links
            where (categoryId = :categoryId)
            $sqlOrder
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':categoryId', $categoryId);
        $stmt->execute();

        // 总是返回一个数组
        // 如果查询结果为空，返回数组长度为0
        return $stmt->fetchAll();

    }

    public function getListOfLinks($order, $limit = 100) {

        // 指定允许的排序字段
        $allowedOrder = [
            "timeCreate",
            "timeLastEdit",
            "timeLastMove",
            "timeLastClick",
        ];

        // 检验字段，不在白名单则用默认值
        if (!in_array($order, $allowedOrder)) {
            $order = "timeCreate";
        }

        // 拼接 SQL
        $sql = <<<BBB
            select * from links
            where $order is not null and trim($order) != ""
            order by $order desc
            limit :limit
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':limit', $limit);
        $stmt->execute();

        return $stmt->fetchAll();

    }

    /**
     * 取得最常访问的12个网址
     * @param $limit
     * @return array|false
     */
    public function getListOfLinksMostOftenClicked($limit = 12) {
        $sql = <<<BBB
            select * from links
            where timesOfClick > 0
            order by timesOfClick desc
            limit :limit
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':limit', $limit);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    /**
     * 取得最近点击过的12个网址
     * @param $limit
     * @return array|false
     */
    public function getListOfLinksRecentlyClicked($limit = 12) {
        $sql = <<<BBB
            select * from links
            where timeLastClick is not null
            order by timeLastClick desc
            limit :limit
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':limit', $limit);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public function getLinkDetailsById($id) {

        $sql = <<<BBB
            SELECT * FROM links
            WHERE id = :id;
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':id', $id);
        $stmt->execute();
        $result = $stmt->fetch();

        if ($result) {
            return $result;
        } else {
            return false;
        }

    }

    public function checkIfLinkExistsById($id) {

        $sql = <<<BBB
            SELECT * FROM links
            WHERE id = :id;
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':id', $id);
        $stmt->execute();

        $result = $stmt->fetch();
        if ($result) {
            return true;
        } else {
            return false;
        }

    }

    public function getTotalNumberOfLinks() {

        $sql = <<<BBB
            select count(*) from links;
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->execute();

        return $stmt->fetchColumn() ?: 0;

    }


    // =========================================================================
    // clickLog 点击记录
    // =========================================================================

    // -------------------------------------------------------------------------
    // 增
    // -------------------------------------------------------------------------

    public function insertIntoClickLog($arr) {

        extract($arr);

        $sql = <<<BBB
            insert into clickLog (timeClick, linkId, linkUrl, linkTitle)
                        values (:timeClick, :linkId, :linkUrl, :linkTitle)
        BBB;

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':timeClick', $timeClick);
        $stmt->bindParam(':linkId', $linkId);
        $stmt->bindParam(':linkUrl', $linkUrl);
        $stmt->bindParam(':linkTitle', $linkTitle);
        $stmt->execute();

    }

    // -------------------------------------------------------------------------
    // 删
    // -------------------------------------------------------------------------

    /**
     * 清空 clickLog
     * @return void
     */
    public function clearClickLog() {
        $sql = <<<BBB
            delete from sqlite_sequence where name = 'clickLog'
        BBB;
        $stmt = $this->prepare($sql);
        $stmt->execute();
    }

    /**
     * 删除指定 id 的 clickLog
     * @param $id
     * @return void
     */
    public function deleteClickLog($id) {
        $sql = <<<BBB
            delete from clickLog
            where id = :id
        BBB;
        $stmt = $this->prepare($sql);
        $stmt->bindParam(':id', $id);
        $stmt->execute();
    }

    // -------------------------------------------------------------------------
    // 改
    // -------------------------------------------------------------------------

    // -------------------------------------------------------------------------
    // 查
    // -------------------------------------------------------------------------

    public function getClickLogs() {
        $sql = <<<BBB
            select * from clickLog
            order by timeClick desc
        BBB;
        $stmt = $this->prepare($sql);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    // =========================================================================
    // 搜索相关
    // =========================================================================

    public function search($keyword, $position)
    {

        $keyword = '%' . $keyword . '%';

        switch ($position) {
            case "title":
                $sql = <<<BBB
                    select * from links
                    where title like :keyword 
                    order by timeCreate desc
                BBB;
                break;
            case "url":
                $sql = <<<BBB
                    select * from links
                    where url like :keyword 
                    order by timeCreate desc
                BBB;
                break;
            case "description":
                $sql = <<<BBB
                    select * from links
                    where description like :keyword
                    order by timeCreate desc
                BBB;
                break;
        }

        $stmt = $this->prepare($sql);
        $stmt->bindParam(':keyword', $keyword);

        $stmt->execute();
        $rows = $stmt->fetchAll();
        return empty($rows) ? false : $rows;

    }

    // =========================================================================
    // 创建表
    // =========================================================================

    public function createTables() {
        $sqlCreateTableCategory = <<<BBB
            CREATE TABLE category (
                id           INTEGER PRIMARY KEY AUTOINCREMENT
                                     UNIQUE,
                title        TEXT    NOT NULL,
                description  TEXT,
                parentId     INTEGER,
                sortNumber   TEXT    NOT NULL,
                icon         INTEGER,
                timeCreate   TEXT    NOT NULL,
                timeLastEdit TEXT,
                timeLastMove TEXT
            );
        BBB;

        $sqlCreateTableLinks = <<<BBB
            CREATE TABLE links (
                id            INTEGER PRIMARY KEY AUTOINCREMENT
                                      UNIQUE,
                title         TEXT    NOT NULL,
                url           TEXT    NOT NULL,
                categoryId    INTEGER NOT NULL,
                sortNumber    TEXT    NOT NULL,
                timesOfClick  INTEGER,
                description   TEXT,
                icon          INTEGER,
                tag           TEXT,
                timeCreate    TEXT    NOT NULL,
                timeLastEdit  TEXT,
                timeLastMove  TEXT,
                timeLastClick TEXT
            );
        BBB;

        $sqlCreateTableClickLog = <<<BBB
            CREATE TABLE clickLog (
                id        INTEGER PRIMARY KEY AUTOINCREMENT
                                  UNIQUE,
                timeClick TEXT    NOT NULL,
                linkId    INTEGER NOT NULL,
                linkUrl   TEXT    NOT NULL,
                linkTitle TEXT    NOT NULL
            );
        BBB;

        $this->exec($sqlCreateTableCategory);
        $this->exec($sqlCreateTableLinks);
        $this->exec($sqlCreateTableClickLog);
    }

    // =========================================================================
    // 删除表
    // =========================================================================

    public function deleteTables() {
        $sqlDeleteTableCategory = <<<BBB
            DROP TABLE IF EXISTS category;
        BBB;

        $sqlDeleteTableLinks = <<<BBB
            DROP TABLE IF EXISTS links;
        BBB;

        $sqlDeleteTableClickLog = <<<BBB
            DROP TABLE IF EXISTS  clickLog;
        BBB;

        $this->exec($sqlDeleteTableCategory);
        $this->exec($sqlDeleteTableLinks);
        $this->exec($sqlDeleteTableClickLog);
    }

    // =========================================================================
    // 重建表
    // =========================================================================

    public function recreateTables() {
        $this->deleteTables();
        $this->createTables();
    }

}
